package com.yiwei.controller.third;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Logger;

import com.yiwei.controller.AssistantConfig;
import com.yiwei.controller.utils.DateTimeUtil;

public class RuiKangServiceBySqlServer {
	private static Logger log = Logger.getLogger(RuiKangServiceBySqlServer.class.getName());
	// 连接驱动
	private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	// 连接路径
	private static String url = "jdbc:sqlserver://" + AssistantConfig.properties.getProperty("sqlServerIP") + ":"
			+ AssistantConfig.properties.getProperty("sqlServerPort") + ";databaseName="
			+ AssistantConfig.properties.getProperty("sqlServerName");
	// 用户名
	private static String username = AssistantConfig.properties.getProperty("sqlServerUsername");
	// 密码
	private static String password = AssistantConfig.properties.getProperty("sqlServerPassword");

	private static Statement st;
	private static Connection con;

	public RuiKangServiceBySqlServer() {

		start();
	}

	public static void start() {
		try {
			// 加载驱动程序，此处运用隐式注册驱动程序的方法
			Class.forName(driver).newInstance();
			log.info("驱动加载成功");
			con = DriverManager.getConnection(url, username, password);// 创建连接对象
			log.info("连接数据库");
			st = con.createStatement();
			log.info("创建sql执行对象");
//			selectByBookId("4");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据体检号，查询患者信息
	 * 
	 * @param id
	 * @return
	 */
	public static UserInfo selectByBookId(String id) {
		ResultSet rs = null;
		UserInfo userInfo = new UserInfo();
		try {
			// 查询
			rs = st.executeQuery("select * from fundus_patientinfo where tjid = '" + id+"'");
			log.info("ResultSet " + rs);
			while (rs.next()) {
				// 姓名
				String xm = rs.getString("xm") == null ? "" : rs.getString("xm");
				userInfo.setName(xm);
				
				userInfo.setBarCode(id);
				// 性别
				String xb = rs.getString("xb") == null ? "" : rs.getString("xb");
				// 性别
				String phone = rs.getString("dh") == null ? "" : rs.getString("dh");
				// 0 男 1 女，2 未知
				if (xb.equals("男")) {
					xb = "0";
				}else if (xb.equals("女")) {
					xb = "1";
				}else {
					xb = "2";
				}
				userInfo.setGender(xb);
				// 年龄
				String Age = rs.getString("nl") == null ? "" : rs.getString("nl");
				userInfo.setAge(Age);
				// 出生年月日
				String Birthday = rs.getString("csrq") == null ? "" : rs.getString("csrq");
				if(Birthday.indexOf(" ")!=-1) {
					Birthday = Birthday.substring(0,Birthday.indexOf(" "));
				}
				userInfo.setBirthDate(Birthday);
				userInfo.setPhone(phone);

				log.info("姓名   ======   " + xm);
				log.info("性别   ======   " + xb);
				log.info("Age   ======   " + Age);
				log.info("Birthday   ======   " + Birthday);
			}
		} catch (Exception e) {
			e.printStackTrace();
			
			log.info("数据库查询失败");
			log.info(e.toString());
			try {
				con = DriverManager.getConnection(url, username, password);// 创建连接对象
				log.info("连接数据库");
				st = con.createStatement();
				log.info("创建sql执行对象");
				log.info("数据库掉线重连");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return userInfo;
	}

	public void insertData(ResultBean bean) {
		UserInfo result = selectByBookId(bean.getPatientID());
		String TransNo = "TransNo";// 项目编号
		String Auditor = "";// 审核者姓名
		
		bean.setBarCode(result.getBarCode());
		bean.setTransNo(TransNo);
		bean.setCheckUser(result.getName());
		bean.setAuditor(Auditor);
		
		String sql = "INSERT INTO [dbo].[PacsResult] ([PatientID], [BarCode], [TransNo], [Summary], [Result], [ResultFlag], [CheckUser], [Auditor], [AuditDate], [ResultImageBase64]) VALUES ('"
				+ bean.getPatientID() + "', '" + bean.getBarCode() + "', '" + bean.getTransNo() + "', '"
				+ bean.getSummary() + "', '" + bean.getResult() + "', " + bean.getResultFlag() + ", '"
				+ bean.getCheckUser() + "', '" + bean.getAuditor() + "', '" + bean.getAuditDate() + "', N'"
				+ bean.getImgBase64() + "' );";
		int ret = 0;
		try {
			// 先删除，再插入
			String deleteSql = "delete from [dbo].[PacsResult] where BarCode='" + bean.getBarCode() + "'";
			ret = st.executeUpdate(deleteSql);

			ret = st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
//		log.info("sql " + sql + " ret " + ret);
		
		//打印日志强制替换
		bean.setImgBase64("--");
		sql = "INSERT INTO [dbo].[PacsResult] ([PatientID], [BarCode], [TransNo], [Summary], [Result], [ResultFlag], [CheckUser], [Auditor], [AuditDate], [ResultImageBase64]) VALUES ('"
				+ bean.getPatientID() + "', '" + bean.getBarCode() + "', '" + bean.getTransNo() + "', '"
				+ bean.getSummary() + "', '" + bean.getResult() + "', " + bean.getResultFlag() + ", '"
				+ bean.getCheckUser() + "', '" + bean.getAuditor() + "', '" + bean.getAuditDate() + "', N'"
				+ bean.getImgBase64() + "' );";
		log.info("sql " + sql + " ret " + ret);
	}

	public static void main(String[] args) {
//		RuiKangServiceBySqlServer sqlServer = new RuiKangServiceBySqlServer();
//		try {
//			sqlServer.selectByBookId("1");
//		} catch (Exception e) {
//			e.printStackTrace();
//		}

//		RuiKangServiceBySqlServer sqlServer = new RuiKangServiceBySqlServer();
//		try {
//			String PatientID = "PatientID";// 体检号
//			String BarCode = "BarCode";// 条码号
//			String TransNo = "TransNo";// 项目编号
//			String Summary = "Summary"; // 小结，检查诊断
//			String Result = "Result"; // 结果，检查所见
//			String ResultFlag = "0";// 枚举值:O-阴性(正常);1-阳性;2-重大阳性
//			String CheckUser = "CheckUser";// 检查者行吗
//			String Auditor = "Auditor";// 审核者姓名
//			String AuditDate = DateTimeUtil.toDateTimeStr(new Date());// 审核日期
//			String imgBase64 = "1";
//
//			ResultBean b = new ResultBean();
//			b.setPatientID(PatientID);
//			b.setBarCode(BarCode);
//			b.setTransNo(TransNo);
//			b.setSummary(Summary);
//			b.setResult(Result);
//			b.setResultFlag(ResultFlag);
//			b.setCheckUser(CheckUser);
//			b.setAuditor(Auditor);
//			b.setAuditDate(AuditDate);
//			b.setImgBase64(imgBase64);
//			
//
//			sqlServer.insertData(b);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}

	}
}
